﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using TimeTable.Domain.Entity;

namespace TimeTable.Domain.DataAccess
{
    public class DataAccessConnection
    {
        #region Fields and Constructor

        private SqlConnection _connection;

        private DataAccessConnection()
        {
        }

        #endregion

        #region Singleton

        private static DataAccessConnection _instance;

        public static DataAccessConnection Instance
        {
            get { return _instance ?? (_instance = new DataAccessConnection()); }
        }

        public void InitializeConnection(string connectionString)
        {
            _connection = new SqlConnection(connectionString);
        }

        #endregion

        #region For Client

        #region Add

        public void AddDivision(Division division)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, division.Name);
            AddSqlParameter(command, "@head_id", SqlDbType.Int, division.Head);
            AddSqlParameter(command, "@type", SqlDbType.Int, division.Type);

            if (division.Parent != null)
            {
                AddSqlParameter(command, "@parent_id", SqlDbType.Int, division.Parent.ID);
                command.CommandText = "INSERT INTO Divisions (name, parent_id, head_id, type) VALUES (@name, @parent_id, @head_id, @type)";
            }
            else
            {
                command.CommandText = "INSERT INTO Divisions (name, head_id, type) VALUES (@name, @head_id, @type)";
            }

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void AddStudyUnit(StudyUnit unit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@division_id", SqlDbType.Int, unit.Division.ID);
            AddSqlParameter(command, "@students_number", SqlDbType.Int, unit.StudentsNumber);
            AddSqlParameter(command, "@name", SqlDbType.VarChar, unit.Name);
            AddSqlParameter(command, "@type", SqlDbType.Int, unit.Type);

            if (unit.Parent != null)
            {
                AddSqlParameter(command, "@parent_id", SqlDbType.Int, unit.Parent.ID);
                command.CommandText = "INSERT INTO StudyUnits (parent_id, division_id, students_number, name, type) VALUES (@parent_id, @division_id, @students_number, @name, @type)";
            }
            else
            {
                command.CommandText = "INSERT INTO StudyUnits (division_id, students_number, name, type) VALUES (@division_id, @students_number, @name, @type)";
            }

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void AddTeacher(Teacher teacher)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@firstname", SqlDbType.VarChar, teacher.FirstName);
            AddSqlParameter(command, "@lastname", SqlDbType.VarChar, teacher.LastName);
            AddSqlParameter(command, "@patronymic", SqlDbType.VarChar, teacher.Patronymic);
            AddSqlParameter(command, "@passport_series", SqlDbType.Int, teacher.PassportSeries);
            AddSqlParameter(command, "@passport_number", SqlDbType.Int, teacher.PassportNumber);
            AddSqlParameter(command, "@address", SqlDbType.VarChar, teacher.Address);
            AddSqlParameter(command, "@division_id", SqlDbType.Int, teacher.Division.ID);

            command.CommandText = "INSERT INTO Teachers (firstname, lastname, patronymic, passport_series, passport_number, address, division_id) VALUES (@firstname, @lastname, @patronymic, @passport_series, @passport_number, @address, @division_id)";
            
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void AddRoom(Room room)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@building_number", SqlDbType.Int, room.BuildingNumber);
            AddSqlParameter(command, "@room_number", SqlDbType.Int, room.RoomNumber);
            AddSqlParameter(command, "@capacity", SqlDbType.Int, room.Capacity);

            command.CommandText = "INSERT INTO Rooms (building_number, room_number, capacity) VALUES (@building_number, @room_number, @capacity)";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void AddTimeTable(Entity.TimeTable timeTable)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@division_id", SqlDbType.Int, timeTable.Division.ID);
            AddSqlParameter(command, "@begin_date", SqlDbType.DateTime, timeTable.BeginDate);
            AddSqlParameter(command, "@end_date", SqlDbType.DateTime, timeTable.EndDate);

            command.CommandText = "INSERT INTO TimeTables (division_id, begin_date, end_date) VALUES (@division_id, @begin_date, @end_date)";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void AddTimeTableUnit(TimeTableUnit timeTableUnit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@subject", SqlDbType.VarChar, timeTableUnit.Subject);
            AddSqlParameter(command, "@study_unit_id", SqlDbType.Int, timeTableUnit.StudyUnit.ID);
            AddSqlParameter(command, "@teacher_id", SqlDbType.Int, timeTableUnit.Teacher.ID);
            AddSqlParameter(command, "@room_id", SqlDbType.Int, timeTableUnit.Room.ID);
            AddSqlParameter(command, "@time_table_id", SqlDbType.Int, timeTableUnit.TimeTable.ID);
            AddSqlParameter(command, "@begin_time", SqlDbType.DateTime, timeTableUnit.BeginTime);
            AddSqlParameter(command, "@end_time", SqlDbType.DateTime, timeTableUnit.EndTime);
            AddSqlParameter(command, "@day_of_week", SqlDbType.Int, timeTableUnit.DayOfWeek);

            command.CommandText = "INSERT INTO TimeTableUnits (subject, study_unit_id, teacher_id, room_id, time_table_id, begin_time, end_time, day_of_week) VALUES (@subject, @study_unit_id, @teacher_id, @room_id, @time_table_id, @begin_time, @end_time, @day_of_week)";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        #endregion

        #region Remove

        public void Remove(Division division)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, division.ID);

            command.CommandText = "DELETE FROM Divisions WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void RemoveRelatedToDivision(Division division)
        {
            if (division.Type == DivisionType.Chair) return;

            var childs = new List<Division>();

            if (division.Type == DivisionType.University)
            {
                childs = GetDivisionsByType(DivisionType.Faculty);
            }
            else if (division.Type == DivisionType.Faculty)
            {
                childs = GetDivisionsByType(DivisionType.Chair);

                // Remove related study units
                var units = GetAllStudyUnits();

                foreach (var studyUnit in units)
                {
                    if (studyUnit.Division.ID == division.ID)
                    {
                        Remove(studyUnit);
                    }
                }
            }

            // Remove related child divisions
            foreach (var child in childs)
            {
                if (child.Parent.ID == division.ID)
                {
                    Remove(child);
                }
            }
        }

        public void Remove(StudyUnit studyUnit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, studyUnit.ID);

            command.CommandText = "DELETE FROM StudyUnits WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void RemoveRelatedToStudyUnit(StudyUnit studyUnit)
        {
            if (studyUnit.Type == StudyUnitType.Group) return;

            // Remove related child study units
            var childs = GetStudyUnitsByType(StudyUnitType.Group);

            foreach (var child in childs)
            {
                if (child.Parent.ID == studyUnit.ID)
                {
                    Remove(child);
                }
            }

            // Remove related time tables units
            var timeTableUnits = GetTimeTableUnitsByStudyUnit(studyUnit);

            foreach (var timeTableUnit in timeTableUnits)
            {
                if (timeTableUnit.StudyUnit.ID == studyUnit.ID)
                {
                    Remove(timeTableUnit);
                }
            }
        }

        public void Remove(Teacher teacher)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, teacher.ID);

            command.CommandText = "DELETE FROM Teachers WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void RemoveRelatedToTeacher(Teacher teacher)
        {
            // Remove related time tables units
            var timeTableUnits = GetTimeTableUnitsByTeacher(teacher);

            foreach (var timeTableUnit in timeTableUnits)
            {
                if (timeTableUnit.Teacher.ID == teacher.ID)
                {
                    Remove(timeTableUnit);
                }
            }
        }

        public void Remove(Room room)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, room.ID);

            command.CommandText = "DELETE FROM Rooms WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void RemoveRelatedToRoom(Room room)
        {
            // Remove related time tables units
            var timeTableUnits = GetTimeTableUnitsByRoom(room);

            foreach (var timeTableUnit in timeTableUnits)
            {
                if (timeTableUnit.Room.ID == room.ID)
                {
                    Remove(timeTableUnit);
                }
            }
        }

        public void Remove(Entity.TimeTable timeTable)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, timeTable.ID);

            command.CommandText = "DELETE FROM TimeTables WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        public void RemoveRelatedToTimeTable(Entity.TimeTable timeTable)
        {
            var childs = GetTimeTableUnitsByTimeTable(timeTable);

            foreach (var child in childs)
            {
                if (child.TimeTable.ID == timeTable.ID)
                {
                    Remove(child);
                }
            }
        }

        public void Remove(TimeTableUnit timeTableUnit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, timeTableUnit.ID);

            command.CommandText = "DELETE FROM TimeTableUnits WHERE id = @id";

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        #endregion

        #region Exists

        public bool DivisionExists(Division division)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@name", SqlDbType.VarChar, division.Name);
            command.CommandText = "SELECT * FROM Divisions WHERE name = @name";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return result;
        }

        public bool TeacherExists(Teacher teacher)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@passport_series", SqlDbType.Int, teacher.PassportSeries);
            AddSqlParameter(command, "@passport_number", SqlDbType.Int, teacher.PassportNumber);
            command.CommandText = "SELECT * FROM Teachers WHERE passport_series = @passport_series AND passport_number = @passport_number";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return result;
        }

        public bool RoomExists(Room room)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@building_number", SqlDbType.Int, room.BuildingNumber);
            AddSqlParameter(command, "@room_number", SqlDbType.Int, room.RoomNumber);
            command.CommandText = "SELECT * FROM Rooms WHERE building_number = @building_number AND room_number = @room_number";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return result;
        }

        public bool TimeTableExists(Entity.TimeTable timeTable)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@division_id", SqlDbType.Int, timeTable.Division.ID);
            AddSqlParameter(command, "@begin_date", SqlDbType.DateTime, timeTable.BeginDate);
            AddSqlParameter(command, "@end_date", SqlDbType.DateTime, timeTable.EndDate);
            command.CommandText = "SELECT * FROM TimeTables WHERE division_id = @division_id AND ((@begin_date >= begin_date AND @begin_date <= end_date) OR (@end_date >= begin_date AND @end_date <= end_date))";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return result;
        }

        public bool TimeTableUnitExists(TimeTableUnit timeTableUnit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@room_id", SqlDbType.Int, timeTableUnit.Room.ID);
            AddSqlParameter(command, "@begin_time", SqlDbType.DateTime, timeTableUnit.BeginTime);
            AddSqlParameter(command, "@end_time", SqlDbType.DateTime, timeTableUnit.EndTime);
            AddSqlParameter(command, "@day_of_week", SqlDbType.Int, timeTableUnit.DayOfWeek);

            command.CommandText = "SELECT * FROM TimeTableUnits WHERE room_id = @room_id AND day_of_week = @day_of_week AND ((@begin_time >= begin_time AND @begin_time <= end_time) OR (@end_time >= begin_time AND @end_time <= end_time))";

            var result = false;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        result = true;
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return result;
        }

        #endregion

        #region GetByID

        public Division GetDivisionByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Divisions WHERE id = @id";

            var division = new Division();
            var parentId = -1;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        division.ID = reader.GetInt32(0);
                        division.Name = reader.GetString(1);
                        division.Type = (DivisionType)reader.GetInt32(2);
                        if (!reader.IsDBNull(3)) parentId = reader.GetInt32(3);
                        division.Head = reader.GetInt32(4);
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            if (parentId != -1) division.Parent = GetDivisionByID(parentId);

            return division;
        }

        public StudyUnit GetStudyUnitByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM StudyUnits WHERE id = @id";

            var unit = new StudyUnit();
            var parentId = -1;
            var divisionId = 0;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        unit.ID = reader.GetInt32(0);
                        unit.Name = reader.GetString(1);
                        unit.StudentsNumber = reader.GetInt32(2);
                        unit.Type = (StudyUnitType)reader.GetInt32(3);
                        if (!reader.IsDBNull(4)) parentId = reader.GetInt32(4);
                        divisionId = reader.GetInt32(5);
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            if (parentId != -1) unit.Parent = GetStudyUnitByID(parentId);
            unit.Division = GetDivisionByID(divisionId);

            return unit;
        }

        private Teacher GetTeacherByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Teachers WHERE id = @id";

            var teacher = new Teacher();
            var divisionId = 0;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        teacher.ID = reader.GetInt32(0);
                        teacher.FirstName = reader.GetString(1);
                        teacher.LastName = reader.GetString(2);
                        teacher.Patronymic = reader.GetString(3);
                        teacher.PassportSeries = reader.GetInt32(4);
                        teacher.PassportNumber = reader.GetInt32(5);
                        teacher.Address = reader.GetString(6);
                        
                        divisionId = reader.GetInt32(7);
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            teacher.Division = GetDivisionByID(divisionId);

            return teacher;
        }

        private Room GetRoomByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM Rooms WHERE id = @id";

            var room = new Room();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        room.ID = reader.GetInt32(0);
                        room.BuildingNumber = reader.GetInt32(1);
                        room.RoomNumber = reader.GetInt32(2);
                        room.Capacity = reader.GetInt32(3);
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            return room;
        }

        private Entity.TimeTable GetTimeTableByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM TimeTables WHERE id = @id";

            var timeTable = new Entity.TimeTable();
            var divisionId = 0;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        timeTable.ID = reader.GetInt32(0);
                        timeTable.BeginDate = reader.GetDateTime(1);
                        timeTable.EndDate = reader.GetDateTime(2);
                        divisionId = reader.GetInt32(3);
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            timeTable.Division = GetDivisionByID(divisionId);

            return timeTable;
        }

        private TimeTableUnit GetTimeTableUnitByID(int id)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@id", SqlDbType.Int, id);
            command.CommandText = "SELECT * FROM TimeTableUnits WHERE id = @id";

            var timeTableUnit = new TimeTableUnit();
            var timeTableId = 0;
            var studyUnitId = 0;
            var teacherId = 0;
            var roomId = 0;

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    if (reader.Read())
                    {
                        timeTableUnit.ID = reader.GetInt32(0);
                        timeTableUnit.Subject = reader.GetString(1);
                        timeTableUnit.BeginTime = reader.GetDateTime(2);
                        timeTableUnit.EndTime = reader.GetDateTime(3);
                        timeTableUnit.DayOfWeek = (DayOfWeek)reader.GetInt32(4);
                        timeTableId = reader.GetInt32(5);
                        studyUnitId = reader.GetInt32(6);
                        teacherId = reader.GetInt32(7);
                        roomId = reader.GetInt32(8);

                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            timeTableUnit.TimeTable = GetTimeTableByID(timeTableId);
            timeTableUnit.StudyUnit = GetStudyUnitByID(studyUnitId);
            timeTableUnit.Teacher = GetTeacherByID(teacherId);
            timeTableUnit.Room = GetRoomByID(roomId);

            return timeTableUnit;
        }

        #endregion

        #region Get

        public List<Division> GetAllDivisions()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM Divisions";

            var divisions = new List<Division>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        divisions.Add(new Division { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < divisions.Count; index++)
            {
                divisions[index] = GetDivisionByID(divisions[index].ID);
            }

            return divisions;
        }

        public List<Division> GetDivisionsByType(DivisionType type)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@type", SqlDbType.Int, type);

            command.CommandText = "SELECT id FROM Divisions WHERE type = @type";

            var divisions = new List<Division>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        divisions.Add(new Division { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < divisions.Count; index++)
            {
                divisions[index] = GetDivisionByID(divisions[index].ID);
            }

            return divisions;
        }

        public List<StudyUnit> GetAllStudyUnits()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM StudyUnits";

            var studyUnits = new List<StudyUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        studyUnits.Add(new StudyUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < studyUnits.Count; index++)
            {
                studyUnits[index] = GetStudyUnitByID(studyUnits[index].ID);
            }

            return studyUnits;
        }

        public List<StudyUnit> GetStudyUnitsByType(StudyUnitType type)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@type", SqlDbType.Int, type);

            command.CommandText = "SELECT id FROM StudyUnits WHERE type = @type";

            var studyUnits = new List<StudyUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        studyUnits.Add(new StudyUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < studyUnits.Count; index++)
            {
                studyUnits[index] = GetStudyUnitByID(studyUnits[index].ID);
            }

            return studyUnits;
        }

        public List<Teacher> GetAllTeachers()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM Teachers";

            var teachers = new List<Teacher>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        teachers.Add(new Teacher { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < teachers.Count; index++)
            {
                teachers[index] = GetTeacherByID(teachers[index].ID);
            }

            return teachers;
        }

        public List<Teacher> GetTeachersByDivision(Division division)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@division_id", SqlDbType.Int, division.ID);

            command.CommandText = "SELECT id FROM Teachers WHERE division_id = @division_id";

            var teachers = new List<Teacher>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        teachers.Add(new Teacher { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < teachers.Count; index++)
            {
                teachers[index] = GetTeacherByID(teachers[index].ID);
            }

            return teachers;
        }

        public List<Room> GetAllRooms()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM Rooms";

            var rooms = new List<Room>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        rooms.Add(new Room { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < rooms.Count; index++)
            {
                rooms[index] = GetRoomByID(rooms[index].ID);
            }

            return rooms;
        }

        public List<Entity.TimeTable> GetAllTimeTables()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM TimeTables";

            var timeTables = new List<Entity.TimeTable>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTables.Add(new Entity.TimeTable { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTables.Count; index++)
            {
                timeTables[index] = GetTimeTableByID(timeTables[index].ID);
            }

            return timeTables;
        }

        public List<Entity.TimeTable> GetTimeTablesByDivision(Division division)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@division_id", SqlDbType.Int, division.ID);

            command.CommandText = "SELECT id FROM TimeTables WHERE division_id = @division_id";

            var timeTables = new List<Entity.TimeTable>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTables.Add(new Entity.TimeTable { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTables.Count; index++)
            {
                timeTables[index] = GetTimeTableByID(timeTables[index].ID);
            }

            return timeTables;
        }

        public List<TimeTableUnit> GetAllTimeTableUnits()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = "SELECT id FROM TimeTableUnits";

            var timeTableUnits = new List<TimeTableUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTableUnits.Add(new TimeTableUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTableUnits.Count; index++)
            {
                timeTableUnits[index] = GetTimeTableUnitByID(timeTableUnits[index].ID);
            }

            return timeTableUnits;
        }

        public List<TimeTableUnit> GetTimeTableUnitsByTimeTable(Entity.TimeTable timeTable)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@time_table_id", SqlDbType.Int, timeTable.ID);

            command.CommandText = "SELECT id FROM TimeTableUnits WHERE time_table_id = @time_table_id";

            var timeTableUnits = new List<TimeTableUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTableUnits.Add(new TimeTableUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTableUnits.Count; index++)
            {
                timeTableUnits[index] = GetTimeTableUnitByID(timeTableUnits[index].ID);
            }

            return timeTableUnits;
        }

        public List<TimeTableUnit> GetTimeTableUnitsByStudyUnit(StudyUnit studyUnit)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@study_unit_id", SqlDbType.Int, studyUnit.ID);

            command.CommandText = "SELECT id FROM TimeTableUnits WHERE study_unit_id = @study_unit_id";

            var timeTableUnits = new List<TimeTableUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTableUnits.Add(new TimeTableUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTableUnits.Count; index++)
            {
                timeTableUnits[index] = GetTimeTableUnitByID(timeTableUnits[index].ID);
            }

            return timeTableUnits;
        }

        public List<TimeTableUnit> GetTimeTableUnitsByTeacher(Teacher teacher)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@teacher_id", SqlDbType.Int, teacher.ID);

            command.CommandText = "SELECT id FROM TimeTableUnits WHERE teacher_id = @teacher_id";

            var timeTableUnits = new List<TimeTableUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTableUnits.Add(new TimeTableUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTableUnits.Count; index++)
            {
                timeTableUnits[index] = GetTimeTableUnitByID(timeTableUnits[index].ID);
            }

            return timeTableUnits;
        }

        public List<TimeTableUnit> GetTimeTableUnitsByRoom(Room room)
        {
            _connection.Open();

            var command = _connection.CreateCommand();

            AddSqlParameter(command, "@room_id", SqlDbType.Int, room.ID);

            command.CommandText = "SELECT id FROM TimeTableUnits WHERE room_id = @room_id";

            var timeTableUnits = new List<TimeTableUnit>();

            using (var reader = command.ExecuteReader())
            {
                try
                {
                    while (reader.Read())
                    {
                        timeTableUnits.Add(new TimeTableUnit { ID = reader.GetInt32(0) });
                    }
                }
                finally
                {
                    reader.Close();
                    _connection.Close();
                }
            }

            for (var index = 0; index < timeTableUnits.Count; index++)
            {
                timeTableUnits[index] = GetTimeTableUnitByID(timeTableUnits[index].ID);
            }

            return timeTableUnits;
        }

        #endregion

        #endregion

        #region For Editor

        public DataTable OpenTable(string tableName)
        {
            _connection.Open();

            try
            {
                DataTable result;

                var command = _connection.CreateCommand();
                command.CommandText = string.Format("SELECT * FROM {0}", tableName);

                using (var reader = command.ExecuteReader())
                {
                    result = new DataTable();
                    result.Load(reader);
                }

                _connection.Close();

                return result;
            }
            catch(Exception)
            {
                _connection.Close();
                return null;
            }
        }

        public void SaveTable(string tableName, DataTable table)
        {
            _connection.Open();

            try
            {
                var command = _connection.CreateCommand();
                command.CommandText = string.Format("SELECT * FROM {0}", tableName);

                var adapter = new SqlDataAdapter(command);
                new SqlCommandBuilder(adapter);
                adapter.Update(table);
            }
            catch(Exception)
            {
                _connection.Close();
            }

            _connection.Close();
        }

        #endregion

        #region Utils

        public bool InitializeDataBase()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = File.ReadAllText("../../../TimeTable.Domain/SQL/Install.sql");

            var result = true;

            try
            {
                command.ExecuteReader();
            }
            catch (Exception)
            {
                _connection.Close();
                result = false;
            }

            _connection.Close();

            return result;
        }

        public bool DropTables()
        {
            _connection.Open();

            var command = _connection.CreateCommand();
            command.CommandText = File.ReadAllText("../../../TimeTable.Domain/SQL/Uninstall.sql");

            var result = true;

            try
            {
                command.ExecuteReader();
            }
            catch (Exception)
            {
                _connection.Close();
                result = false;
            }

            _connection.Close();

            return result;
        }

        private static void AddSqlParameter(SqlCommand command, String name, SqlDbType type, object value)
        {
            var parameter = new SqlParameter
            {
                ParameterName = name,
                SqlDbType = type,
                Value = value
            };

            command.Parameters.Add(parameter);
        }

        #endregion
    }
}
